import requests
from bs4 import BeautifulSoup
import pymysql
import re
def scrawler(url, selector):

    headers = {
        "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/128.0.0.0 Safari/537.36"
    }
    r = requests.get(url, headers=headers)
    r.encoding='utf-8'
    codes = r.text
    # print(codes)
    bs = BeautifulSoup(codes, 'html.parser')
    res = bs.select(selector=selector)
    txt=' '.join([item.text for item in res])
    txt = remove_unwanted_chars(txt)
    txt = re.split(r',\s+', txt.strip())
    txt = ','.join(txt)
    # return str([item.text for item in res])
    return txt

def remove_unwanted_chars(s):
    # 去除\r\n\xa0以及可能的额外空格
    return s.replace('\r', '').replace('\n', '').replace('\xa0', ' ').strip()


class DBTool():
    def __init__(self):
        self.conn=pymysql.connect(host="127.0.0.1",database="test",
                                  port=3306,user='root',password="system",charset="utf8")
        self.cursor=self.conn.cursor()

    def queryAll(self):
        sql = "select * from journal"
        self.cursor.execute(sql)
        result = self.cursor.fetchall()
        return result


    def insert(self, title, author, abstract, pdfurl, puddate):
        try:
            sql = ("insert into journal values (%s,%s, %s, %s, %s, %s)")
            self.cursor.execute(sql, (None,title, author, abstract, pdfurl, puddate))
            self.conn.commit()

        except Exception as e:
            print("插入失败!"+e)

if __name__ == '__main__':
    url = "http://jst.tsinghuajournals.com/article/2024/4358/20240903.htm"
    abstract = scrawler(url, 'div.abs')
    # article_body = scrawler(url, 'div.article_body')
    author = scrawler(url, '#main_article_body > div:nth-child(4)')
    pud_date = scrawler(url, '#main_article_body > div:nth-child(5) > div.article_meta_other > div.receivedate')
    title = scrawler(url, '#main_article_body > div:nth-child(3)')


    db = DBTool()

    db.insert(title, author, abstract, url, pud_date)
    print(db.queryAll())

